package com.zb.smbmsc.dao.user;

import com.mysql.cj.util.StringUtils;
import com.zb.smbmsc.dao.BaseDao;
import com.zb.smbmsc.pojo.Role;
import com.zb.smbmsc.pojo.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{
    @Override
    public User getUser(Connection connection, String userCode) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;

        String sql = "select * from smbms_user where userCode = ?";
        if(null != connection){
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql, userCode);
            if(resultSet.next()){
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setUserName(resultSet.getString("userName"));
                user.setUserPassword(resultSet.getString("userPassword"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
                user.setUserRole(resultSet.getInt("userRole"));
                user.setCreatedBy(resultSet.getInt("createdBy"));
                user.setCreationDate(resultSet.getTimestamp("creationDate"));
                user.setModifyBy(resultSet.getInt("modifyBy"));
                user.setModifyDate(resultSet.getTimestamp("modifyDate"));
            }
        }
        BaseDao.closeAll(null, preparedStatement, resultSet);
        return user;
    }

    @Override
    public int updatePwd(Connection connection, int userId, String userPassword) throws SQLException {
        PreparedStatement preparedStatement = null;
        String sql = "update smbms_user set userPassword = ? where id = ?";
        int execute = 0;
        if(null != connection) {
            execute = BaseDao.execute(connection, preparedStatement, sql, userPassword, userId);
        }
        BaseDao.closeAll(null, preparedStatement, null);
        return execute;
    }

    @Override
    public int selectUserCount(Connection connection, String userName, String userRole) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int count = 0;
        List<Object> params = new ArrayList<>();
        StringBuffer sql = new StringBuffer("select count(1) count from smbms_user u, smbms_role r where u.userRole = r.id ");
        if (connection != null){
            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userCode like ?");
                params.add("%" + userName + "%");
            }
            if (!StringUtils.isNullOrEmpty(userRole)){
                sql.append(" and r.id = ?");
                params.add(userRole);
            }
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params.toArray());
        }

        if (null != resultSet && resultSet.next()){
            count = resultSet.getInt("count");
        }

        BaseDao.closeAll(null, preparedStatement, resultSet);
        return count;
    }

    @Override
    public List<User> selectUserList(Connection connection, String userName, String userRole, int currentPageNo, int pageSize) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> params = new ArrayList<>();
        List<User> users = new ArrayList<>();
        StringBuffer sql = new StringBuffer("select u.*, r.roleName userRoleName from smbms_user u, smbms_role r where u.userRole = r.id");
        if (connection != null){
            if(!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.userCode like ?");
                params.add("%" + userName + "%");
            }
            if (!StringUtils.isNullOrEmpty(userRole)){
                sql.append(" and r.id = ?");
                params.add(userRole);
            }
            sql.append(" order by u.creationDate desc limit ?, ?");
            params.add((currentPageNo - 1) * pageSize);
            params.add(pageSize);
            resultSet = BaseDao.execute(connection, preparedStatement, resultSet, sql.toString(), params.toArray());
        }

        while (null != resultSet && resultSet.next()){
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUserCode(resultSet.getString("userCode"));
            user.setUserName(resultSet.getString("userName"));
            user.setUserPassword(resultSet.getString("userPassword"));
            user.setGender(resultSet.getInt("gender"));
            user.setBirthday(resultSet.getDate("birthday"));
            user.setPhone(resultSet.getString("phone"));
            user.setAddress(resultSet.getString("address"));
            user.setUserRole(resultSet.getInt("userRole"));
            user.setCreatedBy(resultSet.getInt("createdBy"));
            user.setCreationDate(resultSet.getTimestamp("creationDate"));
            user.setModifyBy(resultSet.getInt("modifyBy"));
            user.setModifyDate(resultSet.getTimestamp("modifyDate"));
            user.setUserRoleName(resultSet.getString("userRoleName"));
            users.add(user);
        }

        BaseDao.closeAll(null, preparedStatement, resultSet);
        return users;
    }

    @Override
    public List<Role> getRoleList(Connection connection) throws SQLException {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList<Role> roleList = new ArrayList<>();

        if(connection != null) {
            String sql = "select * from smbms_role";
            rs = BaseDao.execute(connection, pstm, rs, sql);
            while(rs.next()) {
                Role _role = new Role();
                _role.setId(rs.getInt("id"));
                _role.setRoleName(rs.getString("roleName"));
                _role.setRoleCode(rs.getString("roleCode"));
                roleList.add(_role);
            }
        }
        BaseDao.closeAll(null,pstm,rs);
        return roleList;
    }
}
